package com.gree.framework.service.impl;

import com.gree.common.utils.Constants;
import com.gree.common.utils.DateTime;
import com.gree.common.utils.StringUtils;
import com.gree.framework.entity.SysUser;
import com.gree.framework.service.GscmUserService;
import com.gree.framework.utils.SecurityUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.TableName;
import org.nutz.dao.entity.Record;
import org.nutz.dao.sql.Sql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Service
public class GscmUserServiceImpl implements GscmUserService {

    @Autowired
    private Dao dao;

    @Override
    public SysUser finduserWithClient (String usid, String comp, String client){
        Sql sql = Sqls.create("select usid,nama,pawd,suno,(select nama from $client.$smbse02 where suno = t.suno) dsca,grup," +
                "(select dsca from $client.$smbse03 where grup = t.grup) dscb,dept," +
                "(select dsca from $client.$smbse04 where dept = t.dept) dscc," +
                "(select count(*) from $smbse01lock where usid = t.usid) llck," +
                "@comp comp,(select dsca from $client.smcom00 where comp = @comp) dscd,tela," +
                "telb,telc,emil,indt,exdt,lxdt,teno,buyr,isem,eml2, " +
                "(select avatar from $client.$smbse01ex where usid = @usid) avatar " +
                "from $client.$smbse01 t where t.usid = @usid or t.buyr = @usid");

        sql.setVar("client", client)
                .setVar("smbse03", "smbse03"+ comp)
                .setVar("smbse04", "smbse04"+ comp)
                .setVar("smbse02", "smbse02"+ comp)
                .setVar("smbse01lock", "smbse01lock400")
                .setVar("smbse01", "smbse01"+ comp)
                .setVar("smbse01ex", "smbse01ex"+ comp)
                .setParam("comp", comp)
                .setParam("usid", usid);

        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(SysUser.class));
        List<SysUser> records = dao.execute(sql).getList(SysUser.class);
        return records.size()==0?null:records.get(0);
    }

    @Override
    public List<Record> getAuthWithClient (String usid,String comp, String client){
        SysUser sysUser = finduserWithClient(usid,comp,client);
        Sql sql = Sqls.create("select sb5.roid,sb5.dsca,sb5.rtyp,sb5.pano from " +
                "$client.$smbse05 sb5," +
                "(select roid from $client.$smbse09 where usid=@usid " +
                "union select roid from $client.$smbse12 where grup=@grup " +
                "union select roid from $client.$smbse13 where dept=@dept " +
                "union select roid from $client.$smbse14 where duid=@duid " +
                "and to_char(edat,'yyyy/MM/dd hh24:mi:ss')>@ttime) smrole where sb5.roid=smrole.roid");

        sql.setVar("client", client)
                .setVar("smbse05", "smbse05"+ comp)
                .setVar("smbse09", "smbse09"+ comp)
                .setVar("smbse12", "smbse12"+ comp)
                .setVar("smbse13", "smbse13"+ comp)
                .setVar("smbse14", "smbse14"+ comp)
                .setParam("usid", sysUser.getUsid())
                .setParam("grup", sysUser.getGrup())
                .setParam("dept", sysUser.getDept())
                .setParam("duid", sysUser.getUsid())
                .setParam("ttime", DateTime.getStringNowTime("yyyy/MM/dd HH:mm:ss"));
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(Record.class));
        List<Record> records = dao.execute(sql).getList(Record.class);
        return records;
    }

    @Override
    public SysUser finduser (String usid, String comp){
        Sql sql = Sqls.create("select usid,nama,pawd,suno,(select nama from $smbse02 where suno = t.suno) dsca,grup," +
                "(select dsca from smbse03400 where grup = t.grup) dscb,dept," +
                "(select dsca from smbse04400 where dept = t.dept) dscc," +
                "(select count(*) from $smbse01lock where usid = t.usid) llck," +
                "@comp comp,(select dsca from smcom00 where comp = @comp) dscd,tela," +
                "telb,telc,emil,indt,exdt,lxdt,teno,buyr,isem,eml2, " +
                "(select avatar from $smbse01ex where usid = @usid) avatar " +
                "from $smbse01 t where t.usid = @usid or t.buyr = @usid");

        sql.setVar("smbse02", "smbse02"+ Constants.replaceComp(comp))
                .setVar("smbse01lock", "smbse01lock"+ Constants.replaceComp(comp))
                .setVar("smbse01", "smbse01"+ Constants.replaceComp(comp))
                .setVar("smbse01ex", "smbse01ex"+ Constants.replaceComp("400"))
                .setParam("comp", comp)
                .setParam("usid", usid);

        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(SysUser.class));
        List<SysUser> records = dao.execute(sql).getList(SysUser.class);
        return records.size()==0?null:records.get(0);
    }

    @Override
    public SysUser findSuno(String usid, String comp) {
        Sql sql = Sqls.create("select usid,nama,pawd,suno,(select nama from $smbse02 where suno = t.suno) dsca,grup," +
                "(select dsca from smbse03400 where grup = t.grup) dscb,dept," +
                "(select dsca from smbse04400 where dept = t.dept) dscc," +
                "(select count(*) from $smbse01lock where usid = t.usid) llck," +
                "@comp comp,(select dsca from smcom00 where comp = @comp) dscd,tela," +
                "telb,telc,emil,indt,exdt,lxdt,teno,buyr,isem,eml2, " +
                "(select avatar from $smbse01ex where usid = @usid) avatar " +
                "from $smbse01 t where t.suno is not null and t.usid in (@usid,@usidA)");

        sql.setVar("smbse02", "smbse02"+ Constants.replaceComp(comp))
                .setVar("smbse01lock", "smbse01lock"+ Constants.replaceComp(comp))
                .setVar("smbse01", "smbse01"+ Constants.replaceComp(comp))
                .setVar("smbse01ex", "smbse01ex"+ Constants.replaceComp("400"))
                .setParam("comp", comp)
                .setParam("usid", usid)
                .setParam("usidA", usid+"A");

        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(SysUser.class));
        List<SysUser> records = dao.execute(sql).getList(SysUser.class);
        return records.size()==0?null:records.get(0);
    }

    @Override
    public Record getCompDsca (String comp){
        Record record = dao.fetch("smcom09", Cnd.where("comp","=",comp));
        return record;
    }

    @Override
    public List<Record> getAuth (String usid,String comp){
        SysUser sysUser = finduser(usid,comp);
        Sql sql = Sqls.create("select sb5.roid,sb5.dsca,sb5.rtyp,sb5.pano from smbse05400 sb5," +
                "(select roid from $smbse09 where usid=@usid " +
                "union select roid from smbse12400 where grup=@grup " +
                "union select roid from smbse13400 where dept=@dept " +
                "union select roid from $smbse14 where duid=@duid " +
                "and to_char(edat,'yyyy/MM/dd hh24:mi:ss')>@ttime) smrole where sb5.roid=smrole.roid");

        sql.setVar("smbse09", "smbse09"+ Constants.replaceComp(comp))
                .setVar("smbse14", "smbse14"+ Constants.replaceComp(comp))
                .setParam("usid", sysUser.getUsid())
                .setParam("grup", sysUser.getGrup())
                .setParam("dept", sysUser.getDept())
                .setParam("duid", sysUser.getUsid())
                .setParam("ttime", DateTime.getStringNowTime("yyyy/MM/dd HH:mm:ss"));
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(Record.class));
        List<Record> records = dao.execute(sql).getList(Record.class);
        return records;
    }

    @Override
    public List<Record> getAuthBySuno(String usid, String comp) {
        Sql sql = Sqls.create("select bse05.roid, bse05.dsca, bse05.rtyp, bse05.pano " +
                "from smbse05400 bse05,$SMBSE09 bse09 " +
                "where bse05.roid = bse09.roid and bse09.Usid=@usid " +
                "UNION " +
                "select bse05.roid, bse05.dsca, bse05.rtyp, bse05.pano " +
                "from smbse05400 bse05,$SMBSE10 bse10 " +
                "where bse05.roid = bse10.roid and bse10.suno=@usid ");

        sql.setVar("SMBSE09", "SMBSE09"+ Constants.replaceComp(comp))
                .setVar("SMBSE10", "SMBSE10"+ Constants.replaceComp(comp))
                .setParam("usid", usid);
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(Record.class));
        List<Record> records = dao.execute(sql).getList(Record.class);
        return records;
    }

    @Override
    public int updateUserProfile(SysUser user) {
        Sql sql = Sqls.create(
                "update $SMBSE01 set TELA=@TELA,TELB=@TELB,TELC=@TELC,ISEM=@ISEM,EML2=@EML2 " +
                        "where USID=@USID");
        sql.setVar("SMBSE01", "SMBSE01"+ Constants.replaceComp(user.getComp()))
                .setParam("TELA",user.getTela())
                .setParam("TELB",user.getTelb())
                .setParam("TELC",user.getTelc())
                .setParam("EML2",user.getEml2())
                .setParam("ISEM",user.getIsem())
                .setParam("USID",user.getUsid());
        int updateCount = dao.execute(sql).getUpdateCount();
        return updateCount;
    }



    @Override
    public int updateUser(SysUser user) {
        if (StringUtils.isEmpty(user.getUsid())){
            return 0;
        }
        TableName.set(user.getComp());
        int result = dao.updateIgnoreNull(user);
        TableName.clear();
        return result;
    }

    @Override
    public void updateLxdt(String usid, String comp) {
        SimpleDateFormat sdf = new SimpleDateFormat(
                "yyyy-MM-dd hh:mm:ss");
        String lxdt = sdf.format(new Date());
        Sql sql = Sqls.create(
                "update $SMBSE01 set lxdt = to_date(@lxdt,'yyyy/MM/dd hh24:mi:ss') where USID=@USID");
        sql.setVar("SMBSE01", "SMBSE01"+ Constants.replaceComp(comp))
                .setParam("lxdt",lxdt)
                .setParam("USID",usid);
        dao.execute(sql);
    }


    @Override
    public int updateUserProfileAvatar(String avatarUrl) {
        SysUser user = SecurityUtils.getUser().getUser();
        Sql sql = Sqls.create(
                "update $SMBSE01EX set AVATAR=@AVATAR " +
                        "where USID=@USID");
        sql.setVar("SMBSE01EX", "SMBSE01EX"+ Constants.replaceComp("400"))
                .setParam("AVATAR",avatarUrl)
                .setParam("USID",user.getUsid());
        int updateCount = dao.execute(sql).getUpdateCount();
        if (updateCount == 0){
            sql = Sqls.create(
                    "insert into $SMBSE01EX (usid,avatar) " +
                    "values (@USID,@AVATAR)");
            sql.setVar("SMBSE01EX", "SMBSE01EX"+ Constants.replaceComp("400"))
                    .setParam("AVATAR",avatarUrl)
                    .setParam("USID",user.getUsid());
            dao.execute(sql);
            updateCount = 1;
        }
        return updateCount;
    }

    @Override
    public String getAvatar(String usid,String comp) {
        Sql sql = Sqls.create(
                "select avatar from $SMBSE01EX where USID=@USID"
        );
        sql.setVar("SMBSE01EX", "SMBSE01EX"+ Constants.replaceComp("400"))
                .setParam("USID",usid);
        sql.setCallback(Sqls.callback.str());
        dao.execute(sql);
        return sql.getString();
    }

    @Override
    public Record selectByIdDept(String usid,String comp) {
        Sql sql = Sqls.create("select s1.usid,s1.nama,s1.emil,s1.buyr,s2.dept,s2.dsca from $table1 s1 join $table2 s2 ON s1.dept = s2.dept where s1.usid = @usid");
        sql.setVar("table1","smbse01" + comp);sql.setVar("table2","smbse04" + comp);
        sql.setParam("usid",usid.trim());
        sql.setEntity(dao.getEntity(Record.class));
        sql.setCallback(Sqls.callback.record());
        Record record = dao.execute(sql).getObject(Record.class);
        return record;
    }
}
